package demo2;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.*;

/**
 * Creared with IntelliJ IDEA.
 * Description:
 * User:yxd
 * Date:2022-03-18
 * Time:18:33
 */
public class TestSelect {
    public static void main(String[] args) throws SQLException {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("123456");

        Connection connection = dataSource.getConnection();



        String sql = "select book.name book_name,book.author book_author," +
                " student.name student_name,borrow_info.start_time,borrow_info.end_time" +
                " from borrow_info,book,category,student where borrow_info.book_id = book.id " +
                " and book.category_id = category.id " +
                " and borrow_info.student_id = student.id " +
                " and  category.name = '计算机'";
        PreparedStatement statement = connection.prepareStatement(sql);
        System.out.println(statement);

        ResultSet resultSet =  statement.executeQuery();
        while(resultSet.next()){
            String  bookName  = resultSet.getString("book_name");
            String  bookAuthor = resultSet.getString("book_author");
            String  studentName = resultSet.getString("student_name");
            Timestamp startTime  = resultSet.getTimestamp("start_time");
            Timestamp endTime   = resultSet.getTimestamp("end_time");
            System.out.println("书名:" + bookName +  ",   作者:" + bookAuthor + ",   借阅者:" + studentName +
                            ",   借阅起始日期:" + startTime + ",   结束日期:" + endTime);
        }

        resultSet.close();
        statement.close();
        connection.close();
    }
}
